package com.dao;

import com.entity.Td_tblPartyRelationshipTransfer;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface Td_tblPartyRelationshipTransferDao extends JpaRepository<Td_tblPartyRelationshipTransfer,Integer> {

    /**
     * 获取组织迁移记录
     * @param id 主键
     * @return
     */
    @Query(value = "select * from Td_tblPartyRelationshipTransfer where fd_id=?",nativeQuery = true)
    public Td_tblPartyRelationshipTransfer getByFd_ID(int id);

    /**
     * 获取组织迁移完成的记录(发起)
     * @param Name 用户名
     * @param branchId 组织id
     * @param pageable
     * @return
     */
    @Query(value = "select b.Fd_LoginID,b.fd_branchName, b.fd_username,b.Fd_ImageUrl,a.Fd_Status,a.fd_response from Td_tblPartyRelationshipTransfer a,TD_tblPartyOrgMembers b where a.Fd_LoginID=b.fd_loginId and a.fd_status<>0 and b.fd_userName like ? and a.Fd_FormerBranchID=? order by a.fd_eduitDate DESC",nativeQuery = true)
    public List<Object[]> getFinishByName(String Name,int branchId, Pageable pageable);

    /**
     * 获取组织迁移完成的记录(接收)
     * @param Name 用户名
     * @param branchId 组织id
     * @param pageable
     * @return
     */
    @Query(value = "select b.Fd_LoginID,b.fd_branchName, b.fd_username,b.Fd_ImageUrl,a.Fd_Status,a.fd_response from Td_tblPartyRelationshipTransfer a,TD_tblPartyOrgMembers b where a.Fd_LoginID=b.fd_loginId and a.fd_status<>0 and b.fd_userName like ? and a.Fd_TageBranchID=?",nativeQuery = true)
    public List<Object[]> getBeFinishByName(String Name,int branchId, Pageable pageable);

    /**
     * 获取组织迁移未完成的记录(接收)
     * @param Name 用户名
     * @param branchId 组织id
     * @param pageable
     * @return
     */
    @Query(value = "select b.Fd_LoginID,b.fd_branchName, b.fd_username,b.Fd_ImageUrl,a.fd_id from Td_tblPartyRelationshipTransfer a,TD_tblPartyOrgMembers b where a.Fd_LoginID=b.fd_loginId and a.fd_status=0 and b.fd_userName like ? and a.Fd_TageBranchID=?",nativeQuery = true)
    public List<Object[]> getNoFinishByName(String Name,int branchId, Pageable pageable);

    /**
     * 用户组织关系转移完成总记录数
     * @param loginId 用户id
     * @return
     */
    @Query(value = "select count(*) from Td_tblPartyRelationshipTransfer where Fd_Status=1 and Fd_LoginID=?",nativeQuery = true)
    public int memberTransferCount(int loginId);

    /**
     * 获取组织迁移未完成的记录(所有)
     * @param loginId 用户id
     * @param pageable
     * @return
     */
    @Query(value = "select b.Fd_LoginID,b.fd_branchName, b.fd_username,b.Fd_ImageUrl,fd_status from Td_tblPartyRelationshipTransfer a,TD_tblPartyOrgMembers b where a.Fd_LoginID=b.fd_loginId and a.fd_status<>0 and b.fd_loginId=?",nativeQuery = true)
    public List<Object[]> getNoFinishByLoginId(int loginId, Pageable pageable);
}
